Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Table of Contents


Appendix E
Oracle Tuning Parameters

This appendix lists the Oracle tuning parameters. The appendix first groups them into general areas of use and then sorts them alphabetically within the group. The section headings are the syntax for the parameters. The syntax contains information in the following format:

PARAMETER[option1, option2, option3,etc..]<DEFAULT VALUE>

These parameters are divided into sections based on whether the parameter affects performance, is a parameter that enables system analysis, is a general parameter, and so on. Because there may be some overlap, if a parameter is not in the section where you expected it, keep looking.

Performance Parameters

These parameters change the performance characteristics of the system.

CURSOR_SPACE_FOR_TIME [ TRUE,FALSE ] < FALSE >

CURSOR_SPACE_FOR_TIME causes the system to use more space for cursors, thus increasing performance. This parameter affects both the shared SQL areas and the user’s private SQL area. This parameter speeds performance but uses more memory.

If CURSOR_SPACE_FOR_TIME is TRUE, the shared SQL areas remain pinned in the shared pool as long as an open cursor references them. This parameter should be used only if you have a sufficiently large shared pool to hold all the processes’ cursors simultaneously.

The user’s private SQL area is also retained during cursor execution, thus saving time and I/Os at the expense of memory.

DB_BLOCK_BUFFERS [ 4..65535 ] < 32 buffers >

This parameter controls the number of database block buffers in the SGA. DB_BLOCK_BUFFERS is probably the most significant instance tuning parameter because the majority of I/Os in the system are generated by database blocks. Increasing DB_BLOCK_BUFFERS increases performance at the expense of memory. You can calculate the amount of memory that will be consumed with the following formula:

Buffer Size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE

A larger number of database block buffers in the system creates a higher cache-hit rate, thus reducing the amount of I/O and CPU utilized and improving performance.

DB_BLOCK_CHECKPOINT_BATCH [ 0..DB_BLOCK_WRITE_BATCH ] < DB_BLOCK_WRITE_BATCH / 4 >

This parameter specifies the number of blocks that the DBWR writes in one batch when performing a checkpoint. Setting this value too high causes the system to flood the I/O devices during the checkpoint, severely degrades performance, and increases response times—maybe to unacceptable levels.

You should set DB_BLOCK_CHECKPOINT_BATCH to a level that allows a checkpoint to finish before the next checkpoint occurs. Because setting DB_BLOCK_CHECKPOINT_BATCH to zero causes the default value to be used, changing DB_BLOCK_WRITE_BATCH is automatically reflected in the default value of DB_BLOCK_CHECKPOINT_BATCH.

DB_BLOCK_SIZE [ 1024-8192 (OS dependent) ] < OS dependent >

This parameter specifies in bytes the size of the Oracle database blocks. The typical values are 2048 and 4096. If you set the block size relative to the size of the rows in database, you can reduce I/O. In some types of applications in which large amounts of sequential accesses are done, a larger database block size can be of benefit. This value is of use only at database creation time.

DB_FILE_MULTIBLOCK_READ_COUNT [ number (OS dependent) ] < OS dependent >

DB_FILE_MULTIBLOCK_READ_COUNT specifies the maximum number of blocks read in one I/O during a sequential scan. The default is a function of DB_BLOCK_BUFFERS and PROCESSES. Reasonable values are 4, 16, or 32. The maximum allowed values are OS dependent.

This parameter can be especially useful if you do a large number of table scans such as in a DSS system.

DB_FILE_SIMULTANEOUS_WRITES [ 1..24 ] < 4 >

Specifies the number of simultaneous writes for each database file when written by the DBWR. For disk arrays that handle large numbers of requests in the hardware simultaneously, it is advantageous to set DB_FILE_SIMULTANEOUS_WRITES to its maximum.

DISCRETE_TRANSACTIONS_ENABLED [ TRUE/FALSE ] < FALSE >

This parameter implements a simpler, faster rollback mechanism that, under certain conditions, can improve performance. In this mode, you can obtain greater efficiency but the qualification criteria for what kind of transactions can take advantage of discrete transactions are quite strict.

DML_LOCKS [ 20..unlimited,0 ] < 4 * TRANSACTIONS >

This parameter specifies the maximum number of DML locks. A DML lock is used for each table modification transaction. DML locks are used in the DROP TABLE, CREATE INDEX, and LOCK TABLE IN EXCLUSIVE MODE statements. If the value is set to zero, enqueues are disabled, which improves performance slightly.

LOG_ARCHIVE_BUFFER_SIZE [ 1..OS dependent ] < OS dependent >

When running in ARCHIVELOG mode, this parameter specifies the size of each archival buffer in redo log blocks. This parameter, in conjunction with the LOG_ARCHIVE_BUFFERS parameter, can be used to tune the speed of archiving faster or slower as desired to affect overall system performance.

LOG_ARCHIVE_BUFFERS [ 1..OS dependent ] < OS dependent >

When running in ARCHIVELOG mode, this parameter specifies the number of buffers to allocate to archiving. This parameter is used with the LOG_ARCHIVE_BUFFER_SIZE parameter to control the speed of archiving.

LOG_BUFFER [ OS dependent ] < OS dependent >

LOG_BUFFER specifies the number of bytes allocated to the redo log buffer. Larger values reduce I/Os to the redo log by writing fewer blocks of a larger size. Particularly in a heavily used system, this may help performance.

LOG_CHECPOINT_INTERVAL [ 2..unlimited ] < OS dependent >

This parameter specifies the number of redo log file blocks to be filled to cause a checkpoint to occur. Remember that a checkpoint always happens when a log switch occurs. This parameter can be used to cause checkpoints to occur more frequently. Sometimes, frequent checkpoints have less effect on the system than one large checkpoint when the log switch occurs.

LOG_CHECKPOINT_TIMEOUT [ 0..unlimited ] < OS dependent >

This parameter specifies the maximum amount of time that can pass before another checkpoint must occur. This parameter can also be used to increase the frequency of the checkpoint process, thus changing the overall system affect.

LOG_SIMULTANEOUS_COPIES [ 0..unlimited ] < CPU_COUNT >

LOG_SIMULTANEOUS_COPIES specifies the number of redo buffer copy latches simultaneously available to write log entries. You can have up to two redo copy latches per CPU. This helps the LGWR process keep up with the extra load generated by multiple CPUs.

If this parameter is zero, redo copy latches are turned off and all log entries are copied on the redo allocation latch.

LOG_SMALL_ENTRY_MAX_SIZE [ number (OS dependent) ] < OS dependent >

This parameter specifies the size in bytes of the largest copy to the log buffers that can occur under the redo allocation latch without obtaining the redo buffer copy latch. If LOG_SIMULTANEOUS_COPIES is zero, this parameter is ignored.

OPTIMIZER_MODE [ RULE/COST/FIRST_ROWS/ALL_ROWS ] COST

When set to RULE, this parameter causes rule-based optimization to be used, unless hints are supplied in the query. When set to COST, this parameter causes a cost-based approach for the SQL statement, providing that there are any statistics in the data dictionary. When set to FIRST_ROWS, the optimizer chooses execution plans that minimize response time. When set to ALL_ROWS, the optimizer chooses execution plans that minimize total execution time.

PRE_PAGE_SGA [ TRUE/FALSE ] < FALSE >

When set to TRUE, this parameter specifies that, at instance startup, all pages of the SGA are touched, causing them to be allocated in memory. This increases startup time but reduces page faults during run time. This is useful if you have a large number of processes starting at once. This parameter can increase the system performance in that case by avoiding memory allocation overhead.

ROLLBACK_SEGMENTS [ Any rollback segment names ] < NULL >

ROLLBACK_SEGMENTS specifies one or more rollback segment names to be allocated to this instance. If ROLLBACK_SEGMENTS is not specified, the public rollback segments are used. If you want to move your rollback segments to a different disk device, you must specify it here. The parameter is specified as follows:

ROLLBAC_SEGMENTS = ( roll1, roll2, roll3 )

If you use the Oracle Parallel Server option, you must name different rollback segments for each instance.

ROW_CACHE_CURSORS [ 10..3300 ] < 10 >

This parameter specifies the number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default is usually sufficient unless you have particularly high access to the data dictionary.

ROW_LOCKING [ ALWAYS/INTENT ] < ALWAYS >

The value ALWAYS specifies that only row locks are acquired when a table is updated. If you set this value to INTENT, row locks are acquired on a SELECT FOR UPDATE, but when the update occurs, a table lock is acquired.

SEQUENCE_CACHE_ENTRIES [ 10..32000 ] < 10 >

This parameter specifies the number of sequences that can be cached in the SGA. By caching the sequences, an immediate response is achieved for sequences. Set a large value for SEQUENCE_CACHE_ENTRIES if you have a high concurrency of processes requesting sequences.

SEQUENCE_CACHE_HASH_BUCKETS [ 1..32000 (prime number) ] < 7 >

This parameter specifies the number of buckets to speed up access to sequences in the cache. The cache is arranged as a hash table.

SERIALIZABLE [ TRUE/FALSE ] < FALSE >

If this value is set to TRUE, it causes queries to obtain table-level read locks, which prohibits modifications to that table until the transaction has committed or rolled back the transaction. This mode provides repeatable reads and ensures that, within the transactions, multiple queries to the same data achieve the same result.

With SERIALIZABLE set to TRUE, degree-three consistency is provided. There is a performance penalty paid when you run in this mode, which is usually not necessary.

SESSION_CACHED_CURSORS [ 0..OS dependent ] < 0 >

This parameter specifies the number of session cursors to cache. If parse calls of the same SQL statement are repeated, this can cause the session cursor for that statement to be moved into the session cursor cache. Subsequent calls need not reopen the cursor.

SHARED_POOL_SIZE [ 300KB..OS dependent ] < 3.5Mbytes >

This parameter specifies the size of the shared pool in bytes. The shared pool contains the data dictionary cache (row cache) and the library cache as well as session information. Increasing the size of the shared pool should help performance, at the cost of memory.

SMALL_TABLE_THRESHOLD [ 0..OS dependent ] < 4 >

This parameter specifies the number of buffers available in the SGA for table scans. A small table may be read entirely into cache if it fits in SMALL_TABLE_THRESHOLD number of buffers. When scanning a table larger than this, these buffers are reused immediately. This provides a mechanism to prohibit a single-table scan from taking over the buffer cache.

SORT_AREA_RETAINED_SIZE [ 0..SORT_AREA_SIZE ] < SORT_AREA_SIZE >

SORT_AREA_RETAINED_SIZE defines the maximum amount of session memory in bytes that can be used for an in-memory sort. The memory is released when the last row is fetched from the sort area.

If the sort does not fit in SORT_AREA_RETAINED_SIZE bytes, a temporary segment is allocated and the sort is done in this temporary table. This is called an external (disk) sort. This value is important if sort performance is critical.

SORT_AREA_SIZE [ number of bytes ] < OS dependent >

This value specifies the maximum amount of PGA memory to use for an external sort. This memory is released when the sorted rows are written to disk. Increasing this value increases the performance of large sorts.

Remember that each user process has its own PGA. You can calculate the potential memory usage if all the users are doing a large sort with the following formula:

Potential Memory Usage = SORT_AREA_SIZE * ( number of users doing a large sort)

If very large indexes are being created, you may want to adjust this parameter up.

SORT_SPACEMAP_SIZE [ bytes ] < OS dependent >

The size in bytes of the sort spacemap in the context area. If you have very large indexes, adjust this parameter up. Optimal performance is achieved when this parameter has the following value:

SORT_SPACEMAP_SIZE = ( total-sort-bytes / sort-area-size ) + 64

In this formula, total-sort-bytes has the following value:

total-sort-bytes = record-count * ( sum-of-average-column-sizes +
( 2 * number-of-columns ) )

The number-of-columns include the SELECT list for the ORDER BY, the GROUP BY, and the key list for the CREATE INDEX. Also add a 10 or 20 extra bytes for overhead.

Parallel Query Option Parameters

The following parameters affect the operation of the Parallel Query option, which has been available in Oracle since version 7.1. The Parallel Query option can affect performance of certain operations dramatically.

PARALLEL_DEFAULT_MAX_SCANS [ 0..unlimited ] < OS dependent >

This value specifies the maximum number of query servers to be used by default for a query. This valued is used only if there are no values specified in a PARALLEL hint or in the PARALLEL definition clause. This limits the number of query servers used by default when the value of PARALLEL_DEFAULT_SCANSIZE is used by the query coordinator.

PARALLEL_DEFAULT_SCANSIZE [ 0..OS dependent ] < OS dependent >

This parameter is used to determine the number of query servers to be used for a particular table. The size of the table divided by PARALLEL_DEFAULT_SCANSIZE determines the number of query servers, up to PARALLEL_DEFAULT_MAX_SCANS.

PARALLEL_MAX_SERVERS [ 0..100 ] < OS dependent >

This parameter specifies the maximum number of query servers or parallel recovery processes available for this instance.

PARALLEL_MIN_SERVERS [ 0..PARALLEL_MAX_SERVERS ] < 0 >

This parameter determines the minimum number of query servers for an instance. It is also the number of query servers started at instance startup.

PARALLEL_SERVER_IDLE_TIME [ 0..unlimited ] < OS dependent >

This parameter specifies the number of minutes before Oracle terminates an idle query server process.

RECOVERY_PARALLELISM [ 0..PARALLEL_MAX_SERVERS ] < OS dependent >

This parameter specifies the number of processes to be used for instance or media recovery. A large value can greatly reduce instance recovery time. A value of 0 or 1 indicates that parallel recovery will not be done and that recovery will be serial.

Analysis Tool Parameters

The following parameters turn on special features in Oracle for detailed analysis and debugging.

DB_BLOCK_CHECKSUM [ TRUE/FALSE ] < FALSE >

Setting this parameter to TRUE causes the DBWR and direct loader to calculate a checksum for every block they write to disk. This checksum is written into the header of each block.

DB_LOG_CHECKSUM [ TRUE/FALSE ] < FALSE >

Setting this parameter to TRUE causes the LGWR to calculate a checksum for every block it writes to disk. The checksum is written into the header of the redo block.

DB_BLOCK_LRU_EXTENDED_STATISTICS [ 0..unlimited ] < 0 >

This parameter enables statistics in the X$KCBRBH table to be gathered. These statistics estimate the increased number of database block buffer cache hits for each additional buffer. Any value over zero specifies the number of buffers to estimate the cache hits for. If you are interested in estimating the cache hits for an additional 100 buffers, set this parameter to 100.

This parameter affects performance and should be turned off during normal operation.

DB_BLOCK_LRU_STATISTICS [ TRUE/FALSE ] < FALSE >

This parameter specifies whether statistics are gathered for database block buffer cache-hit estimates as specified in DB_BLOCK_LRU_EXTENDED_STATISTICS. Set this parameter to TRUE when you want to gather these statistics.

EVENT < NULL >

The EVENT parameter modifies the scope of ALTER SESSION SET EVENTS commands so that they pertain to the entire instance rather than just the session. This is an Oracle internal parameter and should be changed only at the direction of Oracle support.

FIXED_DATE [ date string ] < NULL >

FIXED_DATE allows you to set as a constant the Oracle function SYSDATE in the format YYYY-MM-DD-HH24:MI:SS. Use this parameter for debug only. This parameter allows you to test your application’s functionality with certain dates, such as the turn of the century.

SQL_TRACE [ TRUE/FALSE ] < FALSE >

This parameter specifies whether the SQL Trace facility is enabled. The SQL Trace facility can provide valuable information but at the price of some overhead. Use SQL Trace only when you are tracking down a specific problem.

TIMED_STATISTICS [ TRUE/FALSE ] < FALSE >

When TIMED_STATISTICS is set to TRUE, the time-related statistics in the dynamic performance tables are enabled. This information can be quite useful but there is considerable overhead involved. Only enable TIMED_STATISTICS when you are analyzing the system.

General Parameters

These parameters are of a general nature; typically they set limits and do not significantly affect performance—except that they may take up space in the SGA.

BACKGROUND_DUMP_DEST [ path-name ] < OS dependent >

This parameter specifies the destination directory where the debugging trace files for the background processes are written. The background processes log all startup and shutdown messages and errors to these files, as well as any other error logs. A log of all CREATE, ALTER, or DROP statements is also stored here.

BLANK_TRIMMING [ TRUE/FALSE ] < FALSE >

If the value of BLANK_TRIMMING is TRUE, this allows a data assignment of a string variable to a column value that is smaller (assuming that the truncated characters are blank).

CHECKPOINT_PROCESS [ TRUE/FALSE ] < FALSE >

This parameter determines whether the CKPT background process is enabled. During a checkpoint, the headers of all the data files must be updated. This task is usually performed by the LGWR process. The job of writing the blocks to disk belongs to the DBWR process. If you notice that the LGWR is slowing down during checkpoints, it may be necessary to enable CKPT to eliminate the extra work that LGWR is doing.

CLEANUP_ROLLBACK_ENTRIES [ number ] < 20 >

This parameter specifies the number of undo records processed at a time when a rollback occurs. This breaks up the rollback and limits a large rollback from locking out smaller rollbacks.

CLOSE_CHACHED_OPEN_CURSORS [ TRUE/FALSE ] < FALSE >

This parameter specifies whether cursors that have been opened and cached by PL/SQL are automatically closed at commit. A value of FALSE allows these cursors to remain open for further use. If cursors are rarely reused, you can save space in the SGA by setting this value to TRUE. If cursors are reused, you can improve performance by leaving this parameter at the default value of FALSE.

COMPATABLE [ variable ] < release dependent >

Setting this variable guarantees that the DBMS will remain compatible with the specified release. Some features may have to be limited for the compatibility to be maintained.

COMPATIBLE_NO_RECOVERY [ variable ] < release dependent >

This parameter works like the COMPATABLE parameter except that the earlier version (specified as the parameter) may not work on the current database if recovery is necessary.

CONTROL_FILES [ 1..8 filenames ] < OS dependent >

This parameter specifies the path names of one to eight control files. It is recommended that there always be more than one control file and that they exist on different physical devices.

DB_DOMAIN [ extension components of a global db name ] < WORLD >

This parameter specifies the extension components of the global database name consisting of valid identifiers separated by periods (for example, texas.us.widgets.com). This allows multiple divisions to each have an ACCOUNTING database that is uniquely identified by the addition of the domain.

DBLINK_ENCRYPT_LOGIN [ TRUE/FALSE ] < FALSE >

When you connect to another server, Oracle encrypts the password. If the value of DBLINK_ENCRYPT_LOGIN is FALSE and the connection fails, Oracle tries to connect again with a nonencrypted password. If DBLINK_ENCRYPT_LOGIN is TRUE and the connection fails, Oracle does not attempt a reconnection.

DB_FILES [ min: MAXDATAFILES, max OS dependent ] < OS dependent >

This parameter specifies the maximum number of database files that can be open. This value can be reduced if you want to reclaim space in the SGA. There is no performance degradation by leaving this value high, just additional memory usage in the SGA.

DB_NAME [ valid name ] < NULL >

This parameter provides a string up to eight characters in length that specifies the name of the database. The following characters are valid:

  Alphabetic characters
  Numbers
  Underscore (_)
  Pound sign (#)
  Dollar sign ($)

No other characters can be used. Double quotation marks (that may be used in the name specified by the user) are removed; they cannot be part of the name. The characters used in the DB_NAME parameter are case insensitive. Thus, SALES is the same as Sales is the same as sales.

ENQUEUE_RESOURCES [ 10..65535 ] < derived >

This parameter specifies the number of resources that can be locked by the lock manager. The default value is derived from PROCESSES and is usually sufficient. The value is derived from this formula:

PROCESSES <= 3; default values = 20
PROCESSES 4-10; default value = ((PROCESSES -  3) * 5) + 20
PROCESSES > 10; default value = ((PROCESSES - 10) * 2) + 55

If you use a large number of tables, you may have to increase this value. This value should never exceed DML_LOCKS + DDL_LOCKS + 20 (overhead).

GLOBAL_NAMES [ TRUE/FALSE ] < FALSE >

This parameter determines whether a database link is required to have the same name as the database to which it connects. Oracle recommends setting this parameter to TRUE to ensure the use of consistent naming conventions for databases and links.

IFILE [ parameter file name ] < NULL >

This parameter embeds another parameter file into the current parameter file. This can be very useful to separate specific changes from the general changes that you often make. The parameter also allows you to separate different types of parameters such as parallel options.

INIT_SQL_FILES [ SQL file name ] < NULL >

This parameter lists the names of SQL files that should be run immediately after database creation. This parameter can be used to automatically create the data dictionary.

JOB_QUEUE_INTERVAL [ 1..3600 ] < 60 >

This parameter specifies, in seconds, the interval between wake-ups of the SNP background process. The processes run jobs that have been queued.

JOB_QUEUE_KEEP_CONNECTIONS [ 1..10 ] < 0 >

This parameter specifies the number of SNP background processes per instance.

JOB_QUEUE_PROCESSES [ TRUE/FALSE ] < FALSE >

This parameter specifies whether remote connections should be shut down after remote jobs have finished executing.

LICENSE_MAX_SESSIONS [ 0..number of session licenses ] < 0 >

LICENSE_MAX_USERS sets the maximum number of concurrent user sessions allowed. When this limit is reached, only users with RESTRICTED SESSION privilege can connect to the server. A zero value indicates that this constraint is not enforced. Either LICENSE_MAX_USERS or LICENSE_MAX_SESSIONS should be set, not both.

LICENSE_MAX_USERS [ 0..number of user icenses ] < 0 >

LICENSE_MAX_USERS sets the maximum number of concurrent users you can create in the database. When you reach this limit, you can no longer create users. A zero value indicates that this constraint is not enforced. Either LICENSE_MAX_USERS or LICENSE_MAX_SESSIONS should be set, not both.

LICENSE_SESSIONS_WARNING [ 0..LICENSE_MAX_SESSIONS ] < 0 >

Sets a warning limit so that the administrator can be aware that the LICENSE_MAX_SESSIONS limit may soon be reached. After LICENSE_SESSIONS_WARNING number of users have connected, a message is written to the alert log for each additional user connecting in.

LOG_ARCHIVE_DEST [ valid path or device name ] < OS dependent >

When running in ARCHIVELOG mode, this text value specifies the default location and root of the file or tape device to use when archiving redo log files. Archiving to tape is not supported under all operating systems.

LOG_ARCHIVE_FORMAT [ valid filename ] < OS dependent >

This parameter uses a text string and variables to specify the default filename format of the archive log files. This string is appended to the LOG_ARCHIVE_DEST parameter name. The following variables can be used in the string:

%s Log sequence number
%t Thread number

Using uppercase letters (%S, %T) causes the value to be fixed length, padded to the left with zeros. A good value is similar to the following:

LOG_ARCHIVE_FORMAT = 'log%S_%T.arc'

LOG_ARCHIVE_START [ TRUE/FALSE ] < FALSE >

When running in ARCHIVELOG mode, LOG_ARCHIVE_START specifies whether archiving should be started up automatically at instance startup. A setting of TRUE indicates that archiving is automatic; FALSE indicates that archiving is manual.

LOG_CHECKPOINTS_TO_ALERT [ TRUE/FALSE ] < FALSE >

This parameter specifies whether you want to log the checkpoints to the alert log. This can be useful in verifying the frequency of checkpoints.

LOG_FILES [ 2..255 ] < 255 >

This parameter specifies the maximum number of redo log files that can be opened at instance startup. Reducing this value can save some space in the SGA. If this value is set higher than the value of MAXLOGFILES used at database creation, it does not override MAXLOGFILES.

MAX_DUMP_FILE_SIZE [ 0..unlimited ] < 500 blocks >

This parameter specifies the maximum size in OS blocks of any trace file written. Set this if you are worried that trace files may consume too much space.

MAX_ENABLED_ROLES [ 0..48 ] < 20 >

This parameter specifies the maximum number of database roles (including subroles) that a user can enable.

MAX_ROLLBACK_SEGMENTS [ 1..65536 ] < 30 >

This parameter specifies the maximum number of rollback segments that can be online for one instance.

OPEN_CURSORS [ 1..OS limit ] < 50 >

This parameter specifies the maximum number of open cursors that a single user process can have open at once.

OPEN_LINKS [ 0..255 ] < 4 >

This parameter specifies the maximum number of concurrent open connections to remote database processes per user process. This value should exceed the maximum number of remote systems accessed within any single SQL statement.

PROCESSES [ 6 to OS dependent ] < 50 >

This parameter specifies the maximum number of OS user processes that connect to the Oracle instance. This number must take into account the background processes and the login process that started up the instance. Be sure to add an additional 6 processes for the background processes.

REMOTE_LOGIN_PASSWORDFILE [ NONE/SHARED/EXCLUSIVE ] < NONE >

This parameter specifies whether Oracle checks for a password file. A value of NONE indicates that users are authenticated through the operating system. A value of EXCLUSIVE indicates that the password file can be used only by one database and can contain names other than SYS and INTERNAL. Setting this parameter to SHARED allows more than one database to use this password file but only SYS and INTERNAL are recognized by this password file.

SESSIONS [ number ] < 1.1 * PROCESSES >

This parameter specifies the total number of user and system sessions. Because recursive sessions may occur, this number should be set slightly higher than PROCESSES. The DDL_LOCKS is derived from this parameter.

SNAPSHOT_REFRESH_INTERVAL [ 1..3600 ] < 60 >

This parameter specifies the number of seconds between wake-ups for the instance’s snapshot refresh process.

SNAPSHOT_REFRESH_KEEP_CONNECTION [ TRUE/FALSE ] < FALSE >

This parameter specifies whether the snapshot refresh process should keep remote connections after the refresh. If set to FALSE, the remote database connections are closed after the refreshes occur.

SNAPSHOT_REFRESH_PROCESS [ 0..10 ] < 0 >

This parameter specifies the number of snapshot refresh processes per instance. You must set this value to 1 or higher for automatic refreshes. One snapshot refresh process is usually sufficient.

SINGLE_PROCESS [ TRUE/FALSE ] < FALSE >

If SINGLE_PROCESS is set to TRUE, the database instance is brought up in a single user mode. A value of FALSE indicates that the database is brought up in a multiprocess mode.

TEMPORARY_TABLE_LOCKS [ 0..OS dependent ] < SESSIONS >

TEMPORARY_TABLE_LOCKS specifies the number of temporary tables that can be created in the temporary segment space. A temporary table lock is required whenever a sort occurs that cannot be held in memory (that is, the sort exceeds SORT_AREA_RETAINED_SIZE). If your application contains a large number of ORDER BY clauses or you perform a large number of index sorts, you may want to increase this number.

TRANSACTIONS [ number ] < 1.1 * PROCESSES >

This parameter specifies the maximum number of concurrent transactions in the instance. The default value is greater than PROCESSES to provide for recursive transactions. A larger value increases the size of the SGA. If you increase the number of transactions allowed in the system, you may also want to increase the number of rollback segments available.

TRANSACTIONS_PER_ROLLBACK_SEGMENT [ 1..OS dependent ] < 30 >

This value specifies the maximum number of concurrent transactions allowed per rollback segment. You can calculate the minimum number of rollback segments enabled at startup with this formula:

Rollback Segments = TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT

Performance can be improved if there is less contention on rollback segments. In a heavily used system, you may want to reduce TRANSACTIONS_PER_ROLLBACK_SEGMENT to decrease this contention.

USER_DUMP_DEST [ valid path name ] < OS dependent >

USER_DUMP_DEST specifies the path to where the debugging trace files are written.

Multithreaded Server Parameters

These parameters are used if you are using the multithreaded server process.

MTS_DISPATCHERS [ “protocol, number ” ] < NULL >

This parameter specifies the configuration of the dispatcher processes created at startup time. The value of this parameter is a quoted string of two values separated by a comma. The values are the network protocol and the number of dispatchers. Each protocol requires a separate specification. This parameter can be specified multiple times. Here is an example of two dispatcher definitions:

MTS_DISPATCHERS = "tcp, 2"
MTS_DISPATCHERS = "ipx, 1"

MTS_LISTENER_ADDRESS [ configuration ] < NULL >

This parameter specifies the configuration of the listener process addresses. There must be a listener process address for each protocol used in the system. Addresses are specified as the SQL*Net description of the connection address.

Because each connection is required to have its own address, this parameter may be specified several times. Here is an example:

MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=7002))"
MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=ipx)()())"

MTS_MAX_DISPATCHERS [ OS dependent ] < 5 >

This parameter specifies the maximum number of dispatcher processes allowed to run simultaneously.

MTS_MAX_SERVERS [ OS dependent ] < 20 >

This parameter specifies the maximum number of shared server processes allowed to run simultaneously.

MTS_SERVERS [ OS dependent ] < 0 >

This parameter specifies the number of server processes created at instance startup.

MTS_SERVICE [ name ] < DB_NAME >

This parameter specifies the name of the service to be associated with the dispatcher. Using this name in the CONNECT string allows users to connect using the dispatcher. The name should be unique. Do not specify this name in quotes. Usually, it is a good idea to make this name the same as the instance name. Because the dispatcher is tried first, if it is not available, the CONNECT string can still connect the user to the database through a normal database connection.

Distributed Option Parameters

These parameters are meaningful only when you use the distributed option.

COMMIT_POINT_STRENGTH [ 0..255 ] < OS dependent >

This value is used to determine the commit point site when executing a distributed transaction. The site with the highest value for COMMIT_POINT_STRENGTH is the commit point site. The site with the largest amount of critical data should be the commit point site.

DISTRIBUTED_LOCK_TIMEOUT [ 1..unlimited ] < 60 seconds >

DISTRIBUTED_LOCK_TIMEOUT specifies, in seconds, how long distributed transactions should wait for locked resources.

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME [ 1..1800 ] < 200 seconds >

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME specifies, in seconds, how long to hold a remote connection open after a distributed transaction fails. A larger value holds the connection longer but also continues to use local resources, even though the connection may have been severed. Any value larger than 1800 seconds interferes with the reconnection and recovery background processes and will never drop a failed connection.

DISTRIBUTED_TRANSACTIONS [ 0..TRANSACTIONS ] < OS dependent >

DISTRIBUTED_TRANSACTIONS specifies the maximum number of distributed transactions that the database can process concurrently. This value cannot exceed the value of TRANSACTIONS. If you are having problems with distributed transactions because network failures are causing many in-doubt transactions, you may want to limit the number of distributed transactions.

If DISTRIBUTED_TRANSACTIONS is set to zero, no distributed transactions are allowed and the RECO process does not start at instance startup.

REMOTE_OS_AUTHENT [ TRUE/FALSE ] < FALSE >

If this parameter is set to TRUE, it allows authentication to remote systems with the value of OS_AUTHENT_PREFIX.

REMOTE_OS_ROLES [ TRUE/FALSE ] < FALSE >

If this parameter is set to TRUE, it allows remote clients to have their roles managed by the OS. If REMOTE_OS_ROLES is FALSE, roles are managed and identified by the database for the remote system.

Parallel Server Parameters

These parameters are used only in conjunction with the Oracle Parallel Server option.

CACHE_SIZE_THRESHOLD [ number ] < 0.1 * DB_BLOCK_BUFFERS >

This parameter specifies the maximum size of a cached partition table split among the caches of multiple instances. If the partition is larger than this value, the table is not split among the caches.

GC_DB_LOCKS [ 0..unlimited ] < 0 >

This parameter specifies the number of PCM locks allocated. The value of GC_DB_LOCKS should be at least 1 greater than the sum of the locks specified with the parameter GC_FILES_TO_LOCKS.

GC_FILES_TO_LOCKS [ file_number=locks:filename=locks ] < NULL >

This parameter supplies a list of filenames, each specifying how many locks should be allocated for that file. Optionally, the number of blocks and the value EACH can be added to further specify the allocation of the locks.

GC_LCK_PROCS [ 0..10 ] < 1 >

This parameter specifies the number of lock processes (LCK0 to LCK9) to create for the instance. Usually, the default value of 1 is sufficient unless an unusually high number of locks are occurring.

GC_ROLLBACK_LOCKS [ number ] < 20 >

This parameter specifies the number of distributed locks available for each rollback segment. The default value is usually sufficient.

GC_ROLLBACK_SEGMENTS [ number ] < 20 >

GC_ROLLBACK_SEGMENTS specifies the maximum number of rollback segments system wide. This includes all instances in the parallel server system, including the SYSTEM rollback segment.

GC_SAVE_ROLLBACK_LOCKS [ number ] < 20 >

This parameter specifies the number of distributed locks reserved for deferred rollback segments. These deferred rollback segments contain rollback entries for segments taken offline.

GC_SEGMENTS [ number ] < 10 >

This parameter specifies the maximum number of segments that may have space management activities performed by different instances simultaneously.

GC_TABLESPACES [ number ] < 5 >

This parameter specifies the maximum number of tablespaces that can be brought online or offline simultaneously.

INSTANCE_NUMBER [ 1..OS dependent ] < lowest available number >

This parameter specifies a unique number that maps the instance to a group of free space lists.

MAX_COMMIT_PROPAGATION_DELAY

This parameter specifies the maximum amount of time that can pass before the SCN (System Change Number) is changed by the DBWR. This value helps in certain conditions where the SCN may not be refreshed often enough because of a high load from multiple instances.

PARALLEL_DEFAULT_MAX_INSTANCES [ 0..instances ] < OS dependent >

This parameter specifies the default number of instances to spit a table among for parallel query processing. This value is used if the INSTANCES DEFAULT is specified in the table/cluster definition.

THREAD [ 0..max threads ] < 0 >

This parameter specifies the number of the redo thread to be used by this instance. Any number can be used, but the value must be unique within the cluster.

Security Parameters

The following parameters help set up system security; manipulate them to obtain the best mix of efficiency and security.

AUDIT_TRAIL [ NONE,DB,OS ]

The AUDIT_TRAIL parameter enables auditing to the table SYS$AUD$. Auditing causes a record of database and user activity to be logged. Because auditing causes overhead, it limits performance. The amount of overhead and the effect on performance is determined by what and how much is audited. Once AUDIT_TRAIL is enabled, auditing is turned on by the Oracle command AUDIT.

OS_AUTHENT_PREFIX [ ] < OPS$ >

This is the value concatenated to the beginning of the user’s OS login account to give a default Oracle account name. The default value of OPS$ is OS dependent and is provided for backward compatibility with previous Oracle versions. Typically, you use the default or set the value to “” (NULL) to eliminate prefixes altogether.

OS_ROLES [ TRUE/FALSE ] < FALSE >

Setting this parameter to TRUE allows the OS to have control over the username’s roles. If set to FALSE, the username’s roles are controlled by the database.

SQL92_SECURITY [ TRUE/FALSE ] < FALSE >

This parameter specifies whether the table-level SELECT privileges are needed to execute an update or delete that reference’s table column values.

Trusted Oracle7 Parameters

The following parameters apply to the Trusted Oracle7 option.

AUTO_MOUNTING [ TRUE/FALSE ] < TRUE >

When set to TRUE, this parameter specifies that a secondary database is mounted by the primary database whenever a user connected to the primary database requests data from the secondary database.

DB_MOUNT_MODE [ NORMAL/READ_COMPATIBLE ] < NORMAL >

This parameter specifies the access mode to which the database is mounted at instance startup. A value of NORMAL starts the database in normal read-write mode; READ_COMPATIBLE starts the database in read-write mode with the addition that it supports concurrent mounting by one or more read-secure instances.

LABEL_CACHE_SIZE [ number > 50 ] < 50 >

This parameter specifies the cache size for dynamic comparison of labels. This number should be greater than the label-category combinations in the OS and should never be less than 50.

MLS_LABEL_FORMAT [ valid label format ] < sen >

This parameter specifies the format used to display labels. The default value sen specifies sensitive.

OPEN_MOUNTS [ 0..255 ] < 5 >

This parameter specifies the maximum number of databases that an instance can simultaneously mount in OS MAC mode. This value should be large enough to handle all the primary and secondary databases you might mount.

National Language Support Parameters

The following parameters are used in the configuration of National Language Support features.

NLS_CURRENCY [ character string ] < derived from NLS_TERRITORY >

This parameter specifies the string to use as the local currency symbol for the L number format element.

NLS_DATE_FORMAT [ format mask ] < derived from NLS_TERRITORY >

This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The value of this parameter is any valid date format mask. Here is an example:

NLS_DATE_FORMAT = 'DD/MM/YYYY'

NLS_DATE_LANGUAGE [ NLS_LANGUAGE value ] < value for NLS_LANGUAGE >

This parameter determines the language to use for the day and month names and date abbreviations (AM, PM, AD, BC).

NLS_ISO_CURRENCY [ valid NLS_TERRITORY value ] < derived from NLS_TERRITORY>

This parameter defines the string to use as the international currency symbol for the C number format element.

NLS_LANGUAGE [ NLS_LANGUAGE value ] < OS dependent >

This parameter defines the default language of the database. This specifies the language to use for messages, the language of day and month names, symbols to be used for AD, BC, AM, and PM, and the default sorting mechanisms.

NLS_NUMERIC_CHARACTERS [ two characters ] < derived from NLS_TERRITORY >

This parameter defines the characters to be used as the group separator and decimal. The group separator is used to separate the integer groups (that is, hundreds, thousands, millions, and so on). The decimal separator is used to distinguish between the integer and decimal portion of the number. Any two characters can be used but they must be different. The parameter is specified by two characters within single quotes. To set the group separator to , (comma) and the decimal separator to . (period), use the following statement:

NLS_NUMBERIC_CHARACTERS = ',.'

NLS_SORT [ BINARY or named linguistic sort ] < derived from NLS_LANGUAGE >

If this parameter is set to BINARY, the collating sequence for ORDER_BY is based on the numeric values of the characters. A linguistic sort decides the order based on the defined linguistic sort. A binary sort is much more efficient and uses much less overhead.

NLS_TERRITORY [ territory name ] < OS dependent >

This parameter specifies the name of the territory whose conventions are used for day and week numbering. The parameter also provides defaults for other NLS parameters.


Table of Contents


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.